create  view vLastGift as
    select t.BT_ID as ID,
           t.TRANSACTION_DATE,
           t.TRANS_NUMBER,
           sum(t.INVOICE_CREDITS) as AMOUNT
    from Trans  t
    where ((t.JOURNAL_TYPE = 'PAY' and t.TRANSACTION_TYPE = 'AR') or
           (t.JOURNAL_TYPE = 'IN' and t.TRANSACTION_TYPE = 'PAY'))
          and t.SOURCE_SYSTEM = 'FR'
    group by t.BT_ID,t.TRANSACTION_DATE,
             t.TRANS_NUMBER having t.TRANSACTION_DATE =
                (select max(t2.TRANSACTION_DATE)
                 from Trans t2
                 where t2.BT_ID = t.BT_ID  and t2.SOURCE_SYSTEM = 'FR' and
                     ((t2.JOURNAL_TYPE = 'PAY' and t2.TRANSACTION_TYPE = 'AR') or
                      (t2.JOURNAL_TYPE = 'IN'  and t2.TRANSACTION_TYPE = 'PAY')))
                      and t.TRANS_NUMBER=(select max(t2.TRANS_NUMBER)
                                          from Trans t2
                                          where t2.BT_ID = t.BT_ID  and t2.SOURCE_SYSTEM = 'FR' and
                                                t.TRANSACTION_DATE =t2.TRANSACTION_DATE and
                                                ((t2.JOURNAL_TYPE = 'PAY' and t2.TRANSACTION_TYPE = 'AR') or
                                                 (t2.JOURNAL_TYPE = 'IN'  and t2.TRANSACTION_TYPE = 'PAY')))

GO
GRANT REFERENCES ON  [dbo].[vLastGift] TO [IMIS]
GRANT SELECT ON  [dbo].[vLastGift] TO [IMIS]
GRANT INSERT ON  [dbo].[vLastGift] TO [IMIS]
GRANT DELETE ON  [dbo].[vLastGift] TO [IMIS]
GRANT UPDATE ON  [dbo].[vLastGift] TO [IMIS]
GO